package com.app.util;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.UnsupportedEncodingException;
import java.security.Key;
import java.security.spec.AlgorithmParameterSpec;
import java.util.Base64;

import javax.crypto.Cipher;
import javax.crypto.SecretKeyFactory;
import javax.crypto.spec.DESKeySpec;
import javax.crypto.spec.IvParameterSpec;
/**
 * 
 * @author:	liangpeng
 * @date:	2019年5月6日 下午1:44:06
 */
public class ExcelUtil {
	/** 总行数 */
	private int totalRows = 0;

	/** 总列数 */
	private int totalCells = 0;

	/** 错误信息 */
	private String errorInfo;

	public int getTotalRows() {
		return totalRows;
	}

	public int getTotalCells() {

		return totalCells;

	}

	public String getErrorInfo() {
		return errorInfo;

	}
	
	/**
	 * 验证excel版本
	 * @param filePath
	 * @return
	 */
	public static boolean isExcel2003(String filePath) {
		return filePath.matches("^.+\\.(?i)(xls)$");

	}

	/**
	 * 验证excel版本
	 * @param filePath
	 * @return
	 */
	public static boolean isExcel2007(String filePath) {
		return filePath.matches("^.+\\.(?i)(xlsx)$");
	}

	/**
	 * 验证excel格式
	 * @param filePath
	 * @return
	 */
	public boolean validateExcel(String filePath) {
		/** 检查文件名是否为空或者是否是Excel格式的文件 */
		if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
			errorInfo = "文件名不是excel格式";
			return false;
		}
		/** 检查文件是否存在 */
		File file = new File(filePath);
		if (file == null || !file.exists()) {
			System.out.println("------" + filePath);
			errorInfo = "文件不存在";
			return false;
		}
		return true;
	}

	/**
	 * 读取excel内容
	 * @param filePath
	 * @return
	 */
	public List<List<List<String>>> read(String filePath) {
		List<List<List<String>>> dataLst = new ArrayList<List<List<String>>>();
		BufferedInputStream is = null;
		try {
			/** 验证文件是否合法 */
			if (!validateExcel(filePath)) {
				System.out.println(errorInfo);
				return null;
			}

			/** 判断文件的类型，是2003还是2007 */
			boolean isExcel2003 = true;
			if (isExcel2007(filePath)) {
				isExcel2003 = false;
			}

			/** 调用本类提供的根据流读取的方法 */
			File file = new File(filePath);
			is = new BufferedInputStream(new FileInputStream(file));
			dataLst = read(is, isExcel2003);
			is.close();

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (is != null) {
				try {
					is.close();
				} catch (IOException e) {
					is = null;
					e.printStackTrace();
				}
			}
		}
		/** 返回最后读取的结果 */
		return dataLst;
	}

	/**
	 * 读取excel内容
	 * @param inputStream
	 * @param isExcel2003
	 * @return
	 */
	public List<List<List<String>>> read(BufferedInputStream inputStream, boolean isExcel2003) {
		List<List<List<String>>> dataLst = null;
		try {
			/** 根据版本选择创建Workbook的方式 */
			Workbook wb = null;
			if (isExcel2003) {
				wb = new HSSFWorkbook(inputStream);
			} else {
				wb = new XSSFWorkbook(inputStream);
			}
			dataLst = read(wb);
		} catch (IOException e) {
			e.printStackTrace();
		}

		return dataLst;

	}

	/**
	 * 读取并解析excel内容
	 * @param wb
	 * @return
	 */
	private List<List<List<String>>> read(Workbook wb) {
		/** 得到第一个shell */
		int sheetCount = wb.getNumberOfSheets();
		List<List<List<String>>> dataLstSheet = new ArrayList<List<List<String>>>();
		for (int i = 0; i < sheetCount; i++) {
			List<List<String>> dataLst = new ArrayList<List<String>>();
			Sheet sheet = wb.getSheetAt(i);
			/** 得到Excel的行数 */
			this.totalRows = sheet.getLastRowNum() + 1;
			/** 得到Excel的列数 */
			if (this.totalRows >= 1 && sheet.getRow(0) != null) {
				this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
			}

			/** 循环Excel的行 */
			for (int r = 0; r < this.totalRows; r++) {
				Row row = sheet.getRow(r);
				if (row == null) {
					continue;
				}
				if (sheet.getRow(r).getPhysicalNumberOfCells() > this.totalCells) {
					this.totalCells = sheet.getRow(r).getPhysicalNumberOfCells();
				}
				List<String> rowLst = new ArrayList<String>();
				/** 循环Excel的列 */
				for (int c = 0; c < this.getTotalCells(); c++) {
					Cell cell = row.getCell(c);
					String cellValue = "";
					if (null != cell) {
						// 以下是判断数据的类型
						switch (cell.getCellType()) {
						case HSSFCell.CELL_TYPE_NUMERIC: // 数字
							if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
								SimpleDateFormat sdf = null;
								if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
									sdf = new SimpleDateFormat("HH:mm");
								} else {// 日期
									sdf = new SimpleDateFormat("yyyy-MM-dd");
								}
								Date date = cell.getDateCellValue();
								cellValue = sdf.format(date);

							} else if (cell.getCellStyle().getDataFormat() == 58) {
								// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
								SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
								double value = cell.getNumericCellValue();
								Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
								cellValue = sdf.format(date);
							} else {
								double value = cell.getNumericCellValue();
								CellStyle style = cell.getCellStyle();
								DecimalFormat format = new DecimalFormat();
								String temp = style.getDataFormatString();
								// 单元格设置成常规
								if (temp.equals("General")) {
									format.applyPattern("#");
								}
								// cellValue = format.format(value);
								cellValue = String.valueOf((int)value);
							}
							// cellValue = cell.getNumericCellValue() + "";
							break;
						case HSSFCell.CELL_TYPE_STRING: // 字符串
							cellValue = cell.getStringCellValue();
							break;
						case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
							cellValue = cell.getBooleanCellValue() + "";
							break;
						case HSSFCell.CELL_TYPE_FORMULA: // 公式
							cellValue = cell.getCellFormula() + "";
							break;
						case HSSFCell.CELL_TYPE_BLANK: // 空值
							cellValue = "";
							break;
						case HSSFCell.CELL_TYPE_ERROR: // 故障
							cellValue = "非法字符";
							break;
						default:
							cellValue = "未知类型";
							break;
						}
					}
					rowLst.add(cellValue);
				}
				/** 保存第r行的第c列 */
				dataLst.add(rowLst);
			}
			dataLstSheet.add(dataLst);
		}
		return dataLstSheet;
	}

	public static void main(String[] args) throws Exception {
		ExcelUtil poi = new ExcelUtil();
		// List<List<String>> list = poi.read("d:/aaa.xls");
		String filePath = "E:/WXChat/WXWork Files/WeChat Files/liangpneg/FileStorage/File/2019-05/奥克斯测试环境用户信息(1).xlsx";
		List<List<List<String>>> lis = poi.read(filePath);
		List<List<String>> list = lis.get(0);
		if (list != null) {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection("jdbc:mysql://120.132.103.35:3306/evaluationsys", "root", "noahark@8225");
			PreparedStatement ps = null;
			int num = 0;
			StringBuffer sql = null;
			CryptoTools tool = new CryptoTools();
			for (int i = 1; i < list.size(); i++) {
				System.out.print("第" + (i) + "行");
				List<String> cellList = list.get(i);
				for (int j = 0; j < cellList.size(); j++) {
					// System.out.print(" 第" + (j + 1) + "列值：");
					System.out.print("    " + cellList.get(j));
				}
				System.out.println();
				sql = new StringBuffer();
				sql.append("UPDATE t_cc_platform_user ");
				sql.append(String.format(" SET EmployeeNumber = '%s'", cellList.get(0)));
				sql.append(String.format(" WHERE LoginName = '%s'", cellList.get(5)));
				sql.append(String.format(" AND UserName = '%s'", tool.encodeNet(cellList.get(1))));
				System.out.println(sql);
				ps = conn.prepareStatement(sql.toString());
				num += ps.executeUpdate();
			}
			System.out.println(num);
			ps.close();
			conn.close();
		}
	}
}




class CryptoTools
{
	private final byte[] DESkey = "1q@w#e$R".getBytes("UTF-8");
	private final byte[] DESIV  = "1q@w#e$R".getBytes("UTF-8");
	
	 private static final char[] HEX_CHAR = {'0', '1', '2', '3', '4', '5',
	            '6', '7', '8', '9', 'a', 'b', 'c', 'd', 'e', 'f'};

	private AlgorithmParameterSpec iv = null;// 加密算法的参数接口，IvParameterSpec是它的一个实现
	private Key key = null;

	public CryptoTools() throws Exception
	{
		DESKeySpec keySpec = new DESKeySpec(DESkey);// 设置密钥参数
		iv = new IvParameterSpec(DESIV);// 设置向量
		SecretKeyFactory keyFactory = SecretKeyFactory.getInstance("DES");// 获得密钥工厂
		key = keyFactory.generateSecret(keySpec);// 得到密钥对象

	}

	public String encode(String data) throws Exception
	{
		Cipher enCipher = Cipher.getInstance("DES/CBC/PKCS5Padding");// 得到加密对象Cipher
		enCipher.init(Cipher.ENCRYPT_MODE, key, iv);// 设置工作模式为加密模式，给出密钥和向量
		byte[] pasByte = enCipher.doFinal(data.getBytes("utf-8"));

		return Base64.getEncoder().encodeToString(pasByte);
	}

	public String decode(String data) throws Exception
	{
		Cipher deCipher = Cipher.getInstance("DES/CBC/PKCS5Padding");
		deCipher.init(Cipher.DECRYPT_MODE, key, iv);

		byte[] pasByte = deCipher.doFinal(Base64.getDecoder().decode(data));

		return new String(pasByte, "UTF-8");
	}
	
	 /**
     * 自定义编码
     **/
    public static byte[] privateDecode(String encryptValue) throws Exception {
        String hexString = encryptValue.replaceAll(" ", "");

        if ((hexString.length() % 2) != 0)
            hexString += " ";

        byte[] returnBytes = new byte[hexString.length() / 2];

        for (int i = 0; i < returnBytes.length; i++) {
            returnBytes[i] = (byte) Integer.valueOf(hexString.substring(i * 2, i * 2 + 2), 16).intValue();
        }

        return returnBytes;
    }
    
    /**
     * 字节转16进制
     *
     * @param encryptValue
     * @return
     * @throws Exception
     */
    public static String privateEncode(String encryptValue) throws Exception {
        byte[] bytes = encryptValue.getBytes();
        char[] buf = new char[bytes.length * 2];
        int index = 0;
        for (byte b : bytes) { // 利用位运算进行转换，可以看作方法一的变种
            buf[index++] = HEX_CHAR[b >>> 4 & 0xf];
            buf[index++] = HEX_CHAR[b & 0xf];
        }
        return new String(buf);
    }
    /**
     * 加密
     *
     * @param encryptValue
     * @return
     * @throws Exception
     */
    public String encodeNet(String encryptValue) throws Exception
    {
        return privateEncode(encode(encryptValue));
    }
}
